package com.aischool.service;

import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.ehcache.CacheKit;

public class WelcomeService {

	private final String CK_CacheName = "CacheUtil";
	
	
	public List<Record> getAdminCount() {
		return Db.findByCache(CK_CacheName, "admin_count_all", getAdminCountSql());
	}
	
	private String getAdminCountSql() {
		return "SELECT 'org' AS code, COUNT(org_id)as item_num, to_char(current_date, 'MMDD') AS add_date FROM ai_org\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'org' AS code, COUNT(org_id)as item_num, to_char((current_date - INTERVAL '1 days'), 'MMDD') AS add_date FROM ai_org WHERE add_time < CURRENT_DATE \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'org' AS code, COUNT(org_id)as item_num, to_char((current_date - INTERVAL '2 days'), 'MMDD') AS add_date FROM ai_org WHERE add_time < (current_date - INTERVAL '1 days')\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'org' AS code, COUNT(org_id)as item_num, to_char((current_date - INTERVAL '3 days'), 'MMDD') AS add_date FROM ai_org WHERE add_time < (current_date - INTERVAL '2 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'org' AS code, COUNT(org_id)as item_num, to_char((current_date - INTERVAL '4 days'), 'MMDD') AS add_date FROM ai_org WHERE add_time < (current_date - INTERVAL '3 days') \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'org' AS code, COUNT(org_id)as item_num, to_char((current_date - INTERVAL '5 days'), 'MMDD') AS add_date FROM ai_org WHERE add_time < (current_date - INTERVAL '4 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'org' AS code, COUNT(org_id)as item_num, to_char((current_date - INTERVAL '6 days'), 'MMDD') AS add_date FROM ai_org WHERE add_time < (current_date - INTERVAL '5 days')  \r\n" + 
				"\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char(current_date, 'MMDD') AS add_date FROM ai_class\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '1 days'), 'MMDD') AS add_date FROM ai_class WHERE add_time < CURRENT_DATE \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '2 days'), 'MMDD') AS add_date FROM ai_class WHERE add_time < (current_date - INTERVAL '1 days')\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '3 days'), 'MMDD') AS add_date FROM ai_class WHERE add_time < (current_date - INTERVAL '2 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '4 days'), 'MMDD') AS add_date FROM ai_class WHERE add_time < (current_date - INTERVAL '3 days') \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '5 days'), 'MMDD') AS add_date FROM ai_class WHERE add_time < (current_date - INTERVAL '4 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '6 days'), 'MMDD') AS add_date FROM ai_class WHERE add_time < (current_date - INTERVAL '5 days')  \r\n" + 
				"\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char(current_date, 'MMDD') AS add_date FROM ai_article\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '1 days'), 'MMDD') AS add_date FROM ai_article WHERE add_time < CURRENT_DATE \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '2 days'), 'MMDD') AS add_date FROM ai_article WHERE add_time < (current_date - INTERVAL '1 days')\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '3 days'), 'MMDD') AS add_date FROM ai_article WHERE add_time < (current_date - INTERVAL '2 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '4 days'), 'MMDD') AS add_date FROM ai_article WHERE add_time < (current_date - INTERVAL '3 days') \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '5 days'), 'MMDD') AS add_date FROM ai_article WHERE add_time < (current_date - INTERVAL '4 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '6 days'), 'MMDD') AS add_date FROM ai_article WHERE add_time < (current_date - INTERVAL '5 days')  ";
	}

	public List<Record> getEduCount(int org_id) {
		return Db.findByCache(CK_CacheName, "edu_count_all_" + org_id, getEduCountSql(), org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id, org_id);
	}
	
	private String getEduCountSql() {
		return "SELECT 'class' AS code, COUNT(class_id)as item_num, to_char(current_date, 'MMDD') AS add_date FROM ai_class WHERE org_id = ?\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '1 days'), 'MMDD') AS add_date FROM ai_class WHERE org_id = ? AND add_time < CURRENT_DATE \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '2 days'), 'MMDD') AS add_date FROM ai_class WHERE org_id = ? AND add_time < (current_date - INTERVAL '1 days')\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '3 days'), 'MMDD') AS add_date FROM ai_class WHERE org_id = ? AND add_time < (current_date - INTERVAL '2 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '4 days'), 'MMDD') AS add_date FROM ai_class WHERE org_id = ? AND add_time < (current_date - INTERVAL '3 days') \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '5 days'), 'MMDD') AS add_date FROM ai_class WHERE org_id = ? AND add_time < (current_date - INTERVAL '4 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'class' AS code, COUNT(class_id)as item_num, to_char((current_date - INTERVAL '6 days'), 'MMDD') AS add_date FROM ai_class WHERE org_id = ? AND add_time < (current_date - INTERVAL '5 days')  \r\n" + 
				"\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'student' AS code, COUNT(student_id)as item_num, to_char(current_date, 'MMDD') AS add_date FROM ai_student WHERE org_id = ?\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'student' AS code, COUNT(student_id)as item_num, to_char((current_date - INTERVAL '1 days'), 'MMDD') AS add_date FROM ai_student WHERE org_id = ? AND add_time < CURRENT_DATE \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'student' AS code, COUNT(student_id)as item_num, to_char((current_date - INTERVAL '2 days'), 'MMDD') AS add_date FROM ai_student WHERE org_id = ? AND add_time < (current_date - INTERVAL '1 days')\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'student' AS code, COUNT(student_id)as item_num, to_char((current_date - INTERVAL '3 days'), 'MMDD') AS add_date FROM ai_student WHERE org_id = ? AND add_time < (current_date - INTERVAL '2 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'student' AS code, COUNT(student_id)as item_num, to_char((current_date - INTERVAL '4 days'), 'MMDD') AS add_date FROM ai_student WHERE org_id = ? AND add_time < (current_date - INTERVAL '3 days') \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'student' AS code, COUNT(student_id)as item_num, to_char((current_date - INTERVAL '5 days'), 'MMDD') AS add_date FROM ai_student WHERE org_id = ? AND add_time < (current_date - INTERVAL '4 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'student' AS code, COUNT(student_id)as item_num, to_char((current_date - INTERVAL '6 days'), 'MMDD') AS add_date FROM ai_student WHERE org_id = ? AND add_time < (current_date - INTERVAL '5 days')  \r\n" + 
				"\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char(current_date, 'MMDD') AS add_date FROM ai_article WHERE org_id = ?\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '1 days'), 'MMDD') AS add_date FROM ai_article WHERE org_id = ? AND add_time < CURRENT_DATE \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '2 days'), 'MMDD') AS add_date FROM ai_article WHERE org_id = ? AND add_time < (current_date - INTERVAL '1 days')\r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '3 days'), 'MMDD') AS add_date FROM ai_article WHERE org_id = ? AND add_time < (current_date - INTERVAL '2 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '4 days'), 'MMDD') AS add_date FROM ai_article WHERE org_id = ? AND add_time < (current_date - INTERVAL '3 days') \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '5 days'), 'MMDD') AS add_date FROM ai_article WHERE org_id = ? AND add_time < (current_date - INTERVAL '4 days')  \r\n" + 
				"UNION ALL \r\n" + 
				"SELECT 'article' AS code, COUNT(article_id)as item_num, to_char((current_date - INTERVAL '6 days'), 'MMDD') AS add_date FROM ai_article WHERE org_id = ? AND add_time < (current_date - INTERVAL '5 days')  ";
	}

	public void removeCache() {
		CacheKit.removeAll(CK_CacheName);
	}
	
}
